package lumis.portal.file;

import lumis.portal.PortalException;
import lumis.portal.PortalObjectAlreadyExistsException;
import lumis.portal.PortalObjectNotFoundException;
import lumis.portal.UnexpectedException;
import lumis.portal.dao.DaoException;
import lumis.portal.dao.jdbc.ITransactionJdbc;
import lumis.portal.fileparser.FileMetadata;
import lumis.portal.fileparser.IFileMetadata;
import lumis.util.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;

/**
 * File storing object
 *
 * @version $Revision: 13093 $ $Date: 2011-05-28 18:40:18 -0300 (Sat, 28 May 2011) $
 * @since 4.0.0
 */
public class FileDaoJdbc implements IFileDao
{
	public FileConfig get(String fileId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select serviceInstanceId, name, fullPath, contentType, fileSize, type, height, width, duration, format from lum_File where fileId = ?");
			try
			{
				statement.setString(1, fileId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					if (resultSet.next())
					{
						String serviceInstanceId = resultSet.getString("serviceInstanceId");
						String name = resultSet.getString("name");
						String fullPath = resultSet.getString("fullPath");
						String contentType = resultSet.getString("contentType");
						long fileSize = resultSet.getLong("fileSize");
						int type = resultSet.getInt("type");
						
						FileMetadata metadata = new FileMetadata(); 
						metadata.setHeight(resultSet.getInt("height"));
						metadata.setWidth(resultSet.getInt("width"));
						metadata.setDuration(resultSet.getLong("duration"));
						metadata.setFormat(resultSet.getString("format"));
						
						FileConfig fileConfig = new FileConfig(fileId, name, fullPath, contentType, fileSize, type, serviceInstanceId, metadata);
						return fileConfig;
					}
					else
						return null;

				}
				finally
				{
					resultSet.close();
				}
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public FileConfig findByFullPath(String fullPathCriteria, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select fileId, serviceInstanceId, name, fullPath, contentType, fileSize, type, height, width, duration, format from lum_File where fullPath = ?");
			try
			{
				statement.setString(1, fullPathCriteria);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					if (resultSet.next())
					{
						String fileId = resultSet.getString("fileId");
						String serviceInstanceId = resultSet.getString("serviceInstanceId");
						String name = resultSet.getString("name");
						String fullPath = resultSet.getString("fullPath");
						String contentType = resultSet.getString("contentType");
						long fileSize = resultSet.getLong("fileSize");
						int type = resultSet.getInt("type");
						
						FileMetadata metadata = new FileMetadata(); 
						metadata.setHeight(resultSet.getInt("height"));
						metadata.setWidth(resultSet.getInt("width"));
						metadata.setDuration(resultSet.getLong("duration"));
						metadata.setFormat(resultSet.getString("format"));
						
						FileConfig fileConfig = new FileConfig(fileId, name, fullPath, contentType, fileSize, type, serviceInstanceId, metadata);
						return fileConfig;
					}
					else
						return null;

				}
				finally
				{
					resultSet.close();
				}
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void add(FileConfig fileConfig, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("insert into lum_File " +
					"(fileId, serviceInstanceId, name, fullPath, contentType, fileSize, type, height, width, duration, format) " +
					"values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
			try
			{
				statement.setString(1, fileConfig.getId());
				statement.setString(2, fileConfig.getServiceInstanceId());
				statement.setString(3, fileConfig.getName());
				statement.setString(4, fileConfig.getFullPath());
				statement.setString(5, fileConfig.getContentType());
				statement.setLong(6, fileConfig.getFileSize());
				statement.setInt(7, fileConfig.getType());
				
				IFileMetadata metadata = fileConfig.getMetadata();
				if (metadata != null)
				{
					if (metadata.getHeight() != null)
					{
						statement.setInt(8, metadata.getHeight());
					}
					else
					{
						statement.setNull(8, Types.INTEGER);
					}

					if (metadata.getWidth() != null)
					{
						statement.setInt(9, metadata.getWidth());
					}
					else
					{
						statement.setNull(9, Types.INTEGER);
					}

					if (metadata.getDuration() != null)
					{
						statement.setLong(10, metadata.getDuration());
					}
					else
					{
						statement.setNull(10, Types.BIGINT);
					}

					if (metadata.getFormat() != null)
					{
						statement.setString(11, metadata.getFormat());
					}
					else
					{
						statement.setNull(11, Types.VARCHAR);
					}
				}
				else
				{
					statement.setNull(8, Types.INTEGER);
					statement.setNull(9, Types.INTEGER);
					statement.setNull(10, Types.INTEGER);
					statement.setNull(11, Types.VARCHAR);
				}

				try
				{
					statement.executeUpdate();
				}
				catch (Exception e)
				{
					try
					{
						if(get(fileConfig.getId(), transaction) != null)
							throw new PortalObjectAlreadyExistsException("STR_RESOURCE_ALREADY_EXISTS;" + fileConfig.getId(), e);
						else
							throw e;
					}
					catch (PortalObjectNotFoundException e1)
					{
						throw e;
					}
				}
			}
			finally
			{
				statement.close();
			}
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void delete(String fileId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("delete from lum_File where fileId = ?");
			try
			{
				statement.setString(1, fileId);
				statement.executeUpdate();
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void update(FileConfig fileConfig, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("update lum_File set serviceInstanceId = ?, name = ?, fullPath = ?, " +
					"contentType = ?, fileSize = ?, type = ?, height = ?, width = ?, duration = ?, format = ? where fileId = ?");
			try
			{
				statement.setString(1, fileConfig.getServiceInstanceId());
				statement.setString(2, fileConfig.getName());
				statement.setString(3, fileConfig.getFullPath());
				statement.setString(4, fileConfig.getContentType());
				statement.setLong(5, fileConfig.getFileSize());
				statement.setInt(6, fileConfig.getType());
				
				IFileMetadata metadata = fileConfig.getMetadata();
				if (metadata != null)
				{
					if (metadata.getHeight() != null)
					{
						statement.setInt(7, metadata.getHeight());
					}
					else
					{
						statement.setNull(7, Types.INTEGER);
					}

					if (metadata.getWidth() != null)
					{
						statement.setInt(8, metadata.getWidth());
					}
					else
					{
						statement.setNull(8, Types.INTEGER);
					}

					if (metadata.getDuration() != null)
					{
						statement.setLong(9, metadata.getDuration());
					}
					else
					{
						statement.setNull(9, Types.BIGINT);
					}

					if (metadata.getFormat() != null)
					{
						statement.setString(10, metadata.getFormat());
					}
					else
					{
						statement.setNull(10, Types.VARCHAR);
					}
				}
				else
				{
					statement.setNull(7, Types.INTEGER);
					statement.setNull(8, Types.INTEGER);
					statement.setNull(9, Types.INTEGER);
					statement.setNull(10, Types.VARCHAR);
				}

				statement.setString(11, fileConfig.getId());
				
				statement.executeUpdate();
			}
			finally
			{
				statement.close();
			}
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public Collection<String> getIdsByFilesIdAndType(String filesId, int type, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select lum_File.fileId from lum_FilesList, lum_File where lum_FilesList.fileId = lum_File.fileId and lum_FilesList.filesId = ? and lum_File.type = ?");
			try
			{
				statement.setString(1, filesId);
				statement.setInt(2, type);
				Collection<String> returnValues = JdbcUtil.fillListString(statement, "fileId");
				return returnValues;
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}

	public Collection<String> getIdsByFilesId(String filesId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select fileId from lum_FilesList where filesId = ?");
			try
			{
				statement.setString(1, filesId);
				Collection<String> returnValues = JdbcUtil.fillListString(statement, "fileId");
				return returnValues;
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	/**
	 * Return all fileIds from lum_file table.
	 * 
	 * @param transaction the transaction for select query access
	 * @return List of fileIds
	 * @throws PortalException any error occurs
	 * @since 6.0.0
	 */

	public Collection<String> getFileIds(ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select fileId from lum_File");
			try
			{
				Collection<String> returnValues = JdbcUtil.fillListString(statement, "fileId");
				return returnValues;
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}

	public Collection<String> getIdsByServiceInstanceId(String serviceInstanceId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select fileId from lum_File where serviceInstanceId=?");
			try
			{
				statement.setString(1, serviceInstanceId);
				return JdbcUtil.fillListString(statement, "fileId");
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}

	public String getIdByItemInfo(String itemId, String serviceInstanceId, String sourceId, String fieldId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select fileId from lum_File where itemId=? and serviceInstanceId=? and sourceId=? and fieldId=?");
			try
			{
				statement.setString(1, itemId);
				statement.setString(2, serviceInstanceId);
				statement.setString(3, sourceId);
				statement.setString(4, fieldId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					if (resultSet.next())
						return resultSet.getString(1);
					else
						return null;
				}
				finally
				{
					resultSet.close();
				}
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}

	public int getNumberOfServiceInstanceFiles(String serviceInstanceId, 
			ITransaction transaction) throws PortalException
	{
		try
		{
			Connection connection = ((ITransactionJdbc) transaction).getConnection();

			PreparedStatement statement = connection.prepareStatement("select count(*) from lum_File where serviceInstanceId=?");
			try
			{
				statement.setString(1, serviceInstanceId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					resultSet.next();
					return resultSet.getInt(1);
				}
				finally
				{
					resultSet.close();
				}
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}		
}
